﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 创建SQL语句
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Id");
            dt.Columns.Add("Name");
            dt.Columns.Add("Age");
            dt.Rows.Add("1","SB",18);
            dt.Rows.Add("2","sb",20);
            var rows = dt.NewRow();
              var rows2 = dt.NewRow();
            rows["Name"] = "GAOMAO";
            rows2["Name"] = "GAO2";
            dt.Rows.Add(rows);
            dt.Rows.Add(rows2);
            List<string> listSql = new List<string>();
            foreach (DataRow dtRows in dt.Rows)
            {
                listSql.Add( CreatInsertSql("TestTable", dtRows, dt.Columns));
            }
            
        }

        public static string CreatInsertSql(string tableName,DataRow drData,DataColumnCollection dcColmnInfo)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("Insert into " + tableName + " (");
            string strField = string.Empty;
            string strValues = string.Empty;
            foreach (DataColumn drColums in dcColmnInfo)
            {
                if (drColums.ExtendedProperties.ContainsKey("ReadOnly")) continue;

                strField += "," + drColums.ColumnName;
                if ((drColums.DataType == typeof(DateTime) || drColums.DataType == typeof(Double)) && drData[drColums.ColumnName].ToString().Trim() == string.Empty)
                {
                    strValues += ",NULL";
                }
                else
                    strValues += ",'" + drData[drColums.ColumnName].ToString().Replace("'", "’") + "'";
            }
            strField = strField.Substring(1);
            strValues = strValues.Substring(1).Replace("'True'", "'1'").Replace("'False'", "'0'");
            sql.Append(strField + ") Values (" + strValues + ")");
            return sql.ToString();
        }
    }
}
